package com.aity.hisse;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.net.ProxySelector;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.HashSet;
import java.util.ResourceBundle;
import java.util.StringTokenizer;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.apache.log4j.Logger;
import org.w3c.dom.Document;


public class Hisse {

	static Logger log = Logger.getLogger(Hisse.class.getName());
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		Connection connection = null;
		Statement statement = null;
		PreparedStatement deleteStatement = null;
		PreparedStatement insertStatement = null;
		ResultSet rs = null;
		
		try{
			ResourceBundle bundle = ResourceBundle.getBundle("database");
			
			MyProxySelector ps = new MyProxySelector(ProxySelector.getDefault());
			ProxySelector.setDefault(ps);
						
			connection = getConnection(bundle);
			
			configureDatabase(connection);		
			
			
			
			statement = connection.createStatement();
			rs = statement.executeQuery("Select * from Hisseler");
			deleteStatement = connection.prepareStatement("delete from hisse_output where ticker = ?");
			insertStatement = connection.prepareStatement("insert into hisse_output (ticker, hisse_date, open, high, low, close, volume, adjusted_close) values (?, ?, ?, ?, ?, ?, ?, ?)");
			
			DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
			DocumentBuilder documentBuilder = factory.newDocumentBuilder();
			Document document = null;
			
			InputStreamReader streamReader = null;
			BufferedReader buffReader = null;
			BufferedWriter bufferedWriter = null;
			
			while(rs.next()){
				String ticker = rs.getString("ticker");
				int startYear = rs.getInt("start_year");
				int startMonth = rs.getInt("start_month");
				int startDay = rs.getInt("start_day");
				
				int endYear = rs.getInt("end_year");
				int endMonth = rs.getInt("end_month");
				int endDay = rs.getInt("end_day");
				
				log.info(ticker + " " + startDay + "/" + startMonth + "/" + startYear + " " + endDay + "/" + endMonth + "/" + endYear);
				try{
					URL stockURL = new URL("http://ichart.finance.yahoo.com/table.csv?s=" + ticker + "&d=" + (endMonth-1) + "&e=" + endDay + "&f=" + endYear + "&g=d&a=" + (startMonth-1) + "&b=" + startDay + "&c=" + startYear + "&ignore=.csv");
					
					streamReader = null;
					buffReader = null;
					
					try{		
						//get the ticker data per stock
						buffReader = getStockReader(stockURL);

						//create the output file
						bufferedWriter = getWriter(bundle, ticker);
						
						//delete the old data
						deleteStatement.setString(1, ticker);
						int deletedRows = deleteStatement.executeUpdate();
						log.info("Deleted this many rows: " + deletedRows);
						deleteStatement.clearParameters();
						
						writeCSV(buffReader, bufferedWriter, ticker, insertStatement);
						
						
						bufferedWriter.flush();
						bufferedWriter.close();

					}catch(IOException ie){
						log.error("IO problems: ", ie.fillInStackTrace());
					}catch(SQLException sqle){
						log.error("SQL problems: ", sqle.fillInStackTrace());
						
						while(sqle != null){
							log.error(sqle);
							Throwable t = sqle.getCause();
							while(t != null){
								log.error("Cause:" + t);
								t = t.getCause();
							}
							sqle = sqle.getNextException();
						}
						
					}finally{
						if(buffReader != null){
							buffReader.close();
						}
						if(streamReader != null){
							streamReader.close();							
						}
					}
					
					String keyStatisticsString = "http://finance.yahoo.com/q/ks?s=" + ticker + "+Key+Statistics"; 
					URL keyStatistics = new URL(keyStatisticsString);
					log.info("This is the key statistics URL: " + keyStatistics.toString());	
					
					URLConnection keyStatisticsConnection = keyStatistics.openConnection();
					log.info("This is the key statistics URLConnection: " + keyStatisticsConnection.toString());
					
					streamReader = null;
					buffReader = null;
					document = null;
					
					try{
						keyStatisticsConnection.connect();
						log.debug("I just opened the key statistics connection");
						streamReader = new InputStreamReader(keyStatisticsConnection.getInputStream());
						log.debug("I just got the key statistics input stream");
											
						buffReader = new BufferedReader(streamReader);

						String keyStats = null;
						while(buffReader.ready()){
							
							keyStats = buffReader.readLine();
							//log.info(keyStats);
						}
						
						//document = documentBuilder.parse(keyStatisticsString);
					}catch(IOException ie){
						log.error("Problem with opening the stream: ", ie.fillInStackTrace());
					}finally{
						if(buffReader != null){
							buffReader.close();
						}
						if(streamReader != null){
							streamReader.close();							
						}
					}
					
					
				}catch(MalformedURLException e){
					log.error("Problem with URL", e.fillInStackTrace());
				}
				
			}
		}catch(Exception e){
			log.error("Error connecting to database: ", e.fillInStackTrace());
		}finally{
			closeResultSet(rs);
			closeStatement(statement);
			closeStatement(deleteStatement);
			closeStatement(insertStatement);
		}
	}

	public static void writeCSV(BufferedReader buffReader, BufferedWriter bufferedWriter, String ticker, PreparedStatement insertStatement) throws IOException, SQLException{
		StringTokenizer lineTokenizer = null;
		StringTokenizer dateTokenizer = null;
		String firstElement = null;
		String nextElement = null;
		String year = null;
		String month = null;
		String day = null;
		Calendar calendar = Calendar.getInstance();
		Date tickerDate = null;
		
		while(buffReader.ready()){
			String csvLine = buffReader.readLine();
			
			lineTokenizer = new StringTokenizer(csvLine, ",");
			if(lineTokenizer.hasMoreTokens()){
				firstElement = lineTokenizer.nextToken();
				if(firstElement.contains("-")){
					dateTokenizer = new StringTokenizer(firstElement, "-");
					
					year = dateTokenizer.nextToken();
					month = dateTokenizer.nextToken();
					day = dateTokenizer.nextToken();
					
					bufferedWriter.write(month);
					bufferedWriter.write("/");
					bufferedWriter.write(day);
					bufferedWriter.write("/");
					bufferedWriter.write(year);
					bufferedWriter.write(",");
					
					
					calendar.set(Integer.parseInt(year), Integer.parseInt(month)-1, Integer.parseInt(day));
					tickerDate = new Date(calendar.getTimeInMillis());
					
					insertStatement.setString(1, ticker);
					insertStatement.setDate(2, tickerDate);
					
					for(int i = 3; i<=8; i++){
						nextElement = lineTokenizer.nextToken();
						bufferedWriter.write(nextElement);
						if(lineTokenizer.hasMoreTokens()){
							bufferedWriter.write(",");
						}
						//log.info(ticker + " " + tickerDate + " " + i + " " + nextElement);
						insertStatement.setFloat(i, Float.parseFloat(nextElement));
					}
					
					//log.info("Starting new DB row");
					insertStatement.addBatch();
				}else{
					bufferedWriter.write(csvLine);
				}
				bufferedWriter.newLine();		
			}
		}
		insertStatement.executeBatch();
		insertStatement.clearBatch();
		insertStatement.clearParameters();
		
	}
	
	public static BufferedWriter getWriter(ResourceBundle bundle, String ticker) throws IOException{
		String outputPath =  bundle.getString("outputPath");
		File file = new File(outputPath + ticker.toLowerCase().replace("^", "") + ".csv");
		if(file.exists()){
			file.delete();
		}else{
			file.createNewFile();
		}
		log.debug("I just created the file");
		FileWriter fileWriter = new FileWriter(file);
		BufferedWriter bufferedWriter = new BufferedWriter(fileWriter);
		
		return bufferedWriter;
	}
	
	
	public static BufferedReader getStockReader(URL stockURL) throws IOException{
		log.info("This is the URL: " + stockURL.toString());	
		
		URLConnection urlConnection = stockURL.openConnection();
		log.info("This is the URLConnection: " + urlConnection.toString());
		
		InputStreamReader streamReader = null;
		BufferedReader buffReader = null;
		
		urlConnection.connect();
		log.debug("I just opened the connection");
		streamReader = new InputStreamReader(urlConnection.getInputStream());
		log.debug("I just got the input stream");
		
		buffReader = new BufferedReader(streamReader);
		
		return buffReader;
	}
	
	
	public static void configureDatabase(Connection connection){
		
		ResultSet tables = null;
		Statement createHisseler = null;
		Statement createHisseOutput = null;
		
		try{
			DatabaseMetaData metaData = connection.getMetaData();
			
			String [] tableTypes = new String[] {"TABLE"};
			
			tables = metaData.getTables(null, null, null, tableTypes);
			
			String tableName = null;
			HashSet <String> tableSet = new HashSet<String>();
			
			while(tables.next()){
				tableName = tables.getString("TABLE_NAME");
				tableSet.add(tableName);
				log.info("Tables: " + tables.getString("TABLE_CAT") + ", " + tables.getString("TABLE_SCHEM") + ", " + tables.getString("TABLE_NAME") + ", " +  tables.getString("TABLE_TYPE") + ", " + tables.getString("REMARKS"));
			}

			if(tableSet.contains("hisseler")){
				log.info("hisseler exists");
			}else{
				createHisseler = connection.createStatement();
				createHisseler.executeUpdate("CREATE TABLE hisseler (ticker text, company text, gics text, start_year integer, start_month integer, start_day integer, end_year integer, end_month integer, end_day integer) WITH (OIDS=FALSE);");
				log.info("Just created hisseler");
			}
			
			if(tableSet.contains("hisse_output")){
				log.info("hisse_output exists");
			}else{
				createHisseOutput = connection.createStatement();
				createHisseOutput.executeUpdate("CREATE TABLE hisse_output (ticker text, hisse_date timestamp with time zone, open numeric, high numeric, low numeric, close numeric, volume numeric, adjusted_close numeric) WITH (OIDS=FALSE);");
				log.info("Just created hisse_output");
			}
			
		}catch(SQLException e){
			log.error("Error checking table existance", e);
			
		}finally{
			closeResultSet(tables);
			closeStatement(createHisseler);
			closeStatement(createHisseOutput);
		}	
	}
	
	
	public static Connection getConnection(ResourceBundle bundle){
		Connection connection = null;
		
		try{
			/*
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		
			String dataSourceName = bundle.getString("dataSourceName");
			log.info("This is the DSN I'm using: " + dataSourceName);
			
			String dbURL = "jdbc:odbc:" + dataSourceName;
			log.info("This is the dbURL: " + dbURL);
						
			connection = DriverManager.getConnection(dbURL,"","");
			*/
			
			Class.forName("org.postgresql.Driver");
			String postgresURL = "jdbc:postgresql:postgres";
			
			connection = DriverManager.getConnection(postgresURL, "postgres", "1oroksoy1");	
		}catch(Exception e){
			log.error("Error getting the connection", e.fillInStackTrace());
		}
		return connection;
	}
	
	public static void closeConnection(Connection connection){
		try{
			if(connection != null){
				connection.close();		
			}else{
				log.error("The connection was null");
			}
		}catch(SQLException e){
			log.error("Error closing database connection: ", e.fillInStackTrace());
		}
	}
	
	public static void closeStatement(Statement statement){
		try{
			if(statement != null){
				statement.close();		
			}else{
				log.error("The statement was null");
			}
		}catch(SQLException e){
			log.error("Error closing database statement: ", e.fillInStackTrace());
		}
	}
	
	public static void closeResultSet(ResultSet resultSet){
		try{
			if(resultSet != null){
				resultSet.close();		
			}else{
				log.error("The result set was null");
			}
		}catch(SQLException se1){
			log.error("Error closing the result set", se1);
		}
	}
}
